#!/usr/bin/env bash

dt="`date -d "$d  0 days ago "  "+%Y-%m-%d"`"
mysql -uroot -proot -hhadoop2<<EOF
use sqlserver;
select substr(X.transaction_date,1,16),X.station_id,X.dept_name,count(X.department_id),sum(X.item_value)
from
(select Z.transaction_date,Y.station_id,Y.transaction_id,Y.item_value,Y.department_id,C.dept_name
from realtime.transactions_vip as Z
join realtime.transaction_items_notoil_vip as Y
on Z.transaction_id=Y.transaction_id and Z.station_id=Y.station_id
join realtime.products as B
on Y.product_code = B.product_code and  B.station_id = 50006
join realtime.departments as C
on C.station_id = 50006 and B.department_id = C.department_id
)AS X
group by
substr(X.transaction_date,1,16),X.station_id,X.dept_name;
EOF